#!/usr/bin/env python
# -*- coding: utf-8 -*-


'''
autor: joão pedro brites raposo alexandre
data criação: 2011/11/21
linguagem: python
modulo: sql
versão: v1.0
'''

import csv
import sqlite3
import time
from file_csv import Csv

csv_location = '/home/admin1/lp_2011_2012/project_python/rebcsv/reb{0}.csv'

#class to operate with sql files
class Sql:

    #class contructor
    def __init__(self):
        pass

    #-----------------------------------------------------------------
    #metodo para criar a base de dados com a informação em fichiero csv
    #-----------------------------------------------------------------
    def create_database(self, db_file):
        print 'creando base de dados...'
        t1 = time.clock()
        ficheiro = file(db_file, 'w')
        ficheiro.close()
        conexao = sqlite3.connect(db_file)
        c = conexao.cursor()
        stmt = '''CREATE TABLE IF NOT EXISTS docentes (cod_docente INTEGER, nome TEXT, ultimo_grau TEXT, 
                curso TEXT, categoria TEXT, regime_tempo TEXT, ano_lectivo INTEGER, 
                tipo_estabelecimento TEXT, nome_estabelecimento TEXT)'''
        c.execute(stmt)
        conexao.commit()
        conexao.close()
        t2 = time.clock()
        print 'tempo =', t2-t1
        print 'base de dados ok.'


    #-----------------------------------------------------------------
    #metodo para capturar informação do ficheiro csv
    #e carregar a base de dados
    #-----------------------------------------------------------------        
    def load_database(self, db_file):
        try:
            open(db_file, 'r')
            print 'carregar base de dados...'
            t1 = time.clock()
            conexao = sqlite3.connect(db_file)
            c = conexao.cursor()
            a = 0
            #ler ficheiro csv
            for ano in range(11):
                print 'load ano {0}...'.format(ano)
                spamReader = csv.reader(open(csv_location.format(ano), 'rb'), delimiter=',')
                i = 0
                #correr a lista
                for row in spamReader:
                    stmt = '''INSERT INTO docentes VALUES("{0}", "{1}", "{2}", "{3}", "{4}", "{5}", "{6}", "{7}", "{8}")'''.\
                           format(row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8])
                    #eliminar cabeçalhos do ficheiro
                    if row[0] == 'Código do Docente':
                        pass
                    else:
                        try:
                            #carregar a base de dados
                            c.execute(stmt)
                        except:  
                            pass
                        pass
                    pass
                pass
            conexao.commit()
            conexao.close()
            t2 = time.clock()
            print 'tempo =', t2-t1
            print 'base de dados ok.'
            pass
        except:
            print 'ficheiro rebides.db não encontrado'
            pass
   

    #-----------------------------------------------------------------
    #metodo para listar os professores por estabelecimento por ano
    #consulta 1
    #-----------------------------------------------------------------        
    def search_teacher_establisment_year_to_file(self, db_file):
        print 'consultar (lista de professores por estabelecimento por ano)...'
        try:         
            t1 = time.clock()            
            conexao = sqlite3.connect(db_file)
            c = conexao.cursor()
            cmd = '''SELECT nome, tipo_estabelecimento, ano_lectivo FROM docentes
                    ORDER BY ano_lectivo, tipo_estabelecimento'''
            c.execute(cmd)
            data_list = c.fetchall()
            conexao.commit()
            conexao.close()
            t2 = time.clock()
            print 'tempo =', t2-t1
            print 'consulta ok.'
            
            file_name = 'statistics_teacher_per_establisment_per_year.csv'
            structure = 1

            #criar ficheiro com dados da pesquisa
            obj = Csv()
            obj.create_file_from_data_search(file_name, data_list, structure)
            return file_name
        
            pass
        except:
            print 'base de dados não encontrada'
            pass

           
    #-----------------------------------------------------------------
    #metodo para total de professores por estabelecimento por ano
    #contagem 1
    #-----------------------------------------------------------------        
    def search_teacher_establisment_year_to_count(self, db_file):
        print 'consultar (total de professores por estabelecimento por ano)...'
        try:
            t1 = time.clock()
            conexao = sqlite3.connect(db_file)
            c = conexao.cursor()
            cmd = '''SELECT COUNT(nome), tipo_estabelecimento, ano_lectivo FROM docentes
                    GROUP BY tipo_estabelecimento, ano_lectivo
                    ORDER BY tipo_estabelecimento'''
            c.execute(cmd)
            data_list = c.fetchall()
            conexao.commit()
            conexao.close()
            t2 = time.clock()
            print 'tempo =', t2-t1
            print 'consulta ok.'
        
            file_name = 'count_statistics_teacher_per_establisment_per_year.csv'
            structure = 2

            #criar ficheiro com dados da pesquisa
            obj = Csv()
            obj.create_file_from_data_search(file_name, data_list, structure)
            return file_name
        
            pass
        except:
            print 'base de dados não encontrada'
            pass    
 

    #-----------------------------------------------------------------
    #metodo para listar os professores por categoria por estabelecimento por ano
    #consulta 2
    #-----------------------------------------------------------------        
    def search_teacher_categorie_establisment_year_to_file(self, db_file):
        print 'consultar (lista de professores por categoria por estabelecimento por ano)...'
        try:
            t1 = time.clock()
            conexao = sqlite3.connect(db_file)
            c = conexao.cursor()
            cmd = '''SELECT nome, categoria, tipo_estabelecimento, ano_lectivo FROM docentes
                    ORDER BY ano_lectivo, tipo_estabelecimento, categoria'''
            c.execute(cmd)
            data_list = c.fetchall()
            conexao.commit()
            conexao.close()
            t2 = time.clock()
            print 'tempo =', t2-t1
            print 'consulta ok.'
        
            file_name = 'statistics_teacher_per_categorie_per_establisment_per_year.csv'
            structure = 3

            #criar ficheiro com dados da pesquisa
            obj = Csv()
            obj.create_file_from_data_search(file_name, data_list, structure)
            return file_name

            pass
        except:
            print 'base de dados não encontrada'
            pass 


    #-----------------------------------------------------------------
    #metodo para total de professores por categoria por estabelecimento por ano
    #contagem 2
    #-----------------------------------------------------------------        
    def search_teacher_categorie_establisment_year_to_count(self, db_file):
        print 'consultar (total de professores por categoria por estabelecimento por ano)...'
        try:
            t1 = time.clock()
            conexao = sqlite3.connect(db_file)
            c = conexao.cursor()
            cmd = '''SELECT COUNT(nome), categoria, tipo_estabelecimento, ano_lectivo FROM docentes
                    GROUP BY tipo_estabelecimento, categoria, ano_lectivo ORDER BY ano_lectivo'''
            c.execute(cmd)
            data_list = c.fetchall()
            conexao.commit()
            conexao.close()
            t2 = time.clock()
            print 'tempo =', t2-t1
            print 'consulta ok.'
  
            file_name = 'count_statistics_teacher_per_categorie_per_establisment_per_year.csv'
            structure = 4

            data_list.pop() #remove label from list

            #criar ficheiro com dados da pesquisa
            obj = Csv()
            obj.create_file_from_data_search(file_name, data_list, structure)
            return file_name

            pass
        except:
            print 'base de dados não encontrada'
            pass


    #-----------------------------------------------------------------
    #metodo para listar os professores por grau por estabelecimento por ano
    #consulta 3
    #-----------------------------------------------------------------             
    def search_teacher_degree_establisment_year_to_file(self, db_file):
        print 'consultar (lista de professores por grau por estabelecimento por ano)...'
        try:
            t1 = time.clock()
            conexao = sqlite3.connect(db_file)
            c = conexao.cursor()
            cmd = '''SELECT nome, ultimo_grau, tipo_estabelecimento, ano_lectivo FROM docentes
                    ORDER BY ano_lectivo, tipo_estabelecimento, ultimo_grau'''
            c.execute(cmd)
            data_list = c.fetchall()
            conexao.commit()
            conexao.close()
            t2 = time.clock()
            print 'tempo =', t2-t1
            print 'consulta ok.'

            file_name = 'statistics_teacher_per_degree_per_establisment_per_year.csv'
            structure = 5

            #criar ficheiro com dados da pesquisa
            obj = Csv()
            obj.create_file_from_data_search(file_name, data_list, structure)
            return file_name

            pass
        except:
            print 'base de dados não encontrada'
            pass 


    #-----------------------------------------------------------------
    #metodo para total de professores por grau por estabelecimento por ano
    #contagem 3
    #-----------------------------------------------------------------        
    def search_teacher_degree_establisment_year_to_count(self, db_file):
        print 'consultar (total de professores por grau por estabelecimento por ano'
        try:
            t1 = time.clock()
            conexao = sqlite3.connect(db_file)
            c = conexao.cursor()
            cmd = '''SELECT COUNT(nome), ultimo_grau, tipo_estabelecimento, ano_lectivo FROM docentes
                    GROUP BY tipo_estabelecimento, ultimo_grau, ano_lectivo ORDER BY ano_lectivo'''
            c.execute(cmd)
            data_list = c.fetchall()
            conexao.commit()
            conexao.close()
            t2 = time.clock()
            print 'tempo =', t2-t1
            print 'consulta ok.'

            file_name = 'count_statistics_teacher_per_degree_per_establisment_per_year.csv'
            structure = 6

            data_list.pop() #remove label from list

            #criar ficheiro com dados da pesquisa
            obj = Csv()
            obj.create_file_from_data_search(file_name, data_list, structure)
            return file_name

            pass
        except:
            print 'base de dados não encontrada'
            pass
   

    #-----------------------------------------------------------------
    #metodo para listar estabelecimentos
    #-----------------------------------------------------------------        
    def search_establisment(self, db_file):
        print 'consultar de estabelecimentos...'
        try:         
            conexao = sqlite3.connect(db_file)
            c = conexao.cursor()
            cmd = '''SELECT DISTINCT(tipo_estabelecimento) FROM docentes
                    ORDER BY tipo_estabelecimento'''
            c.execute(cmd)
            data_list = c.fetchall()
            conexao.commit()
            conexao.close()
            print 'consulta ok.'
            return data_list

            pass
        except:
            print 'base de dados não encontrada'
            pass


